var Msdb = require("../../../db/Msdb.js");
const log = require('../../../logger.js');
var logger = require('../../../logger.js').logger;
const jwt = require('jsonwebtoken');
const expressJwt = require('express-jwt');
const nodeExcel = require('excel-export');
var Excel = require('exceljs');
var fs = require('fs');
var multer = require('multer');
var UUID = require('uuid');
var request = require("request");
var Redisdb = require("../../../db/Redisdb.js");

//春华汽配领域业务转化

/**
 * 发货通知单更新销售订单
 * @returns   
 */
async function use_qdgmx_qgd() {
	return new Promise(async function (resolve, reject) {
		var db = new Msdb();

		//更新请购单主表数量
		let updateQgdsql_qgsl = `
		merge into shipSRM_qgd a using (select mainid,sum(qgsl) zsl  from 
		shipSRM_qgd_mx where qgsl is not null group by mainid  ) b
		on a.id=b.mainid  when matched then update set 
		zsl=b.zsl;`
		await db.update(updateQgdsql_qgsl)
		resolve(true)
	})
}

/**
 * 发货通知单更新销售订单
 * @returns   
 */
async function use_fhtzd_to_xsdd() {
	return new Promise(async function (resolve, reject) {
		var db = new Msdb();

		//从发货通知单明细表更新到销售订单原料明细
		//shipSRM_fhtzd_mx to shipSRM_xsdd_mx
		var updatesql = `
		merge into shipSRM_xsdd_mx a using  (
		select ydmxid,sum(CAST(fhsl AS FLOAT)) fhsl from shipSRM_fhtzd_mx where fhsl is not null group by ydmxid 
		 ) b 
		on a.id=b.ydmxid  and a.create_time >'2024-10-10'
		when matched then update set 
		fhsl=b.fhsl  ;`

		//增加逻辑 如果 没有下推收料单 更新销售订单对应字段为0
		var updatexsddsql2 = `update shipSRM_xsdd_mx set fhsl=0,xtzt=null where id not in (select ydmxid from shipSRM_fhtzd_mx where ydmxid is not null )`

		//更新销售订单主表已发数量
		var updatexsddsql_fhsl = `
		merge into shipSRM_xsdd a using (select mainid,sum(fhsl) fhsl  from 
		shipSRM_xsdd_mx where fhsl is not null group by mainid  ) b
		on a.id=b.mainid  and a.create_time >'2024-10-10'
		when matched then update set 
		zfhsl=b.fhsl;`


		//如果发货数量大于订单数量 更新销售订单状态为关闭
		var updatexsddsql_djzt = `
		update shipSRM_xsdd set djzt='关闭' where zsl<=zfhsl and zsl !=0 
		and create_time >'2024-10-10'`

		// 如果小于改为正常
		var updatexsddsql_djzt2 = `
		update shipSRM_xsdd set djzt='正常' where zsl>zfhsl and zsl !=0 
		and create_time >'2024-10-10'`



		//如果销售明细 存在 xtzt='已下推' 更新销售订单 sfxt='已下推'
		var updatexsddsql_xtzt = `
		update shipSRM_xsdd set sfxt='已下推' where id in 
		(select mainid from shipSRM_xsdd_mx where xtzt='已下推') 	and create_time >'2024-10-10'`

		//如果明细不存在已下推 更新为 null
		var updatexsddsql_xtzt2 = `
		update shipSRM_xsdd set sfxt=null where id not in 
		(select mainid from shipSRM_xsdd_mx where xtzt='已下推') and create_time >'2024-10-10'`

		await db.update(updatesql)
		await db.update(updatexsddsql2)
		await db.update(updatexsddsql_fhsl)
		await db.update(updatexsddsql_djzt)
		await db.update(updatexsddsql_djzt2)
		await db.update(updatexsddsql_xtzt)
		await db.update(updatexsddsql_xtzt2)
		resolve(true)
	})
}

/**
 * 收料报检单更新采购订单
 * @returns   
 */
async function use_sltzd_to_cgdd() {
	return new Promise(async function (resolve, reject) {
		var db = new Msdb();
		//从发货通知单明细表更新到销售订单原料明细
		//shipSRM_fhtzd_mx to shipSRM_xsdd_mx
		var updatesql = `
        merge into shipSRM_cgdd_mx a using  (
                select ydmxid,sum(CAST(slsl AS FLOAT)) slsl from shipSRM_sltzd_mx where slsl is not null group by ydmxid
                 ) b
                on a.id=b.ydmxid and a.create_time >'2024-10-10'
                when matched then update set
                slsl=b.slsl,sysl=a.sl-b.slsl  ;`


		//增加逻辑 如果 没有收料报检单 更新采购订单对应字段为0,下推状态为空
		var updatecdddsql = `update shipSRM_cgdd_mx set slsl=0,sysl=sl,xtzt='' where id not in (select ydmxid from shipSRM_sltzd_mx)`

		//更新采购订单主表收料数量
		var updatecdddsql2 = `
		merge into shipSRM_cgdd a using (select mainid,sum(slsl) slsl  from 
		shipSRM_cgdd_mx where slsl is not null group by mainid  ) b
		on a.id=b.mainid and a.create_time >'2024-10-10'
		when matched then update set
		zslsl=b.slsl;`



		//更新采购订单单据状态 
		var updatedjztsql = `update shipSRM_cgdd set djzt='关闭' where zslsl>=zsl and zsl !=0;
		  update shipSRM_cgdd set djzt='正常' where zslsl<zsl and zsl !=0
		  and create_time >'2024-10-10'
		`
		//更新收料报检单收料数量 
		var updatefhslsql = `
		merge into shipSRM_sltzd a using (select mainid,sum(CAST(slsl AS FLOAT)) slsl  from 
		shipSRM_sltzd_mx where slsl is not null group by mainid  ) b
		on a.id=b.mainid  and a.create_time >'2024-10-10'
		when matched then update set 
		zslsl=b.slsl;`

	
		await db.update(updatesql)
		await db.update(updatecdddsql)
		await db.update(updatecdddsql2)
		await db.update(updatefhslsql)
		await db.update(updatedjztsql)
		resolve(true)
	})
}

/**
 * 销售出库更新销售订单
 * @returns   
 */
async function use_xsckd_to_xsdd() {
	return new Promise(async function (resolve, reject) {
		var db = new Msdb();

		//从发货通知单明细表更新到销售订单原料明细
		//shipSRM_fhtzd_mx to shipSRM_xsdd_mx
		var updatesql = `
		merge into shipSRM_xsdd_mx a using  (
		select b.ydmxid,sum(a.sfsl) sfsl from shipSRM_xsckd_mx a left JOIN shipSRM_fhtzd_mx b ON 
		(a.ydmxid = b.id)
		 where a.sfsl is not null group by b.ydmxid 
		 ) b 
		on a.id=b.ydmxid  and a.create_time >'2024-10-10'
		when matched then update set 
		cksl=b.sfsl  ;`
		var updatexsddsql_cksl = `UPDATE shipSRM_xsdd_mx
SET cksl = 0
WHERE create_time > '2024-10-01'
AND NOT EXISTS (
    SELECT 1
    FROM shipSRM_xsckd_mx a
    LEFT JOIN shipSRM_fhtzd_mx b ON a.ydmxid = b.id
    WHERE b.ydmxid = shipSRM_xsdd_mx.id
    AND b.create_time > '2024-10-01'
    AND a.create_time > '2024-10-01'
);`


		//更新销售订单主表已发数量
		var updatexsddsql = `
		merge into shipSRM_xsdd a using (select mainid,sum(cksl) cksl  from 
		shipSRM_xsdd_mx where cksl is not null group by mainid  ) b
		on a.id=b.mainid  and a.create_time >'2024-10-10'
		when matched then update set 
		zcksl=b.cksl;`

		await db.update(updatesql)
		await db.update(updatexsddsql)
        await db.update(updatexsddsql_cksl)
		await use_xsckd_to_fhtzd()
		resolve(true)
	})
}

/**
 * 销售出库单更新发货通知单
 * @returns   
 */
async function use_xsckd_to_fhtzd() {
	return new Promise(async function (resolve, reject) {
		var db = new Msdb();
		//从发货通知单明细表更新到销售订单原料明细
		//shipSRM_fhtzd_mx to shipSRM_xsdd_mx
		var updatesql = `
		    merge into shipSRM_fhtzd_mx a using  (
		    select ydmxid,sum(CAST(sfsl AS FLOAT)) sfsl from shipSRM_xsckd_mx where sfsl is not null group by ydmxid 
		    ) b 
		    on a.id=b.ydmxid and a.create_time >'2024-10-10'
		    when matched then update set 
			sfsl=b.sfsl;`

			//如果无关联 shipSRM_xsckd_mx数据 shipSRM_fhtzd_mx 的sfsl=0
			var updatefhtzdsql_sfsl = `update shipSRM_fhtzd_mx set sfsl=0 where id not in (select ydmxid from shipSRM_xsckd_mx where ydmxid is not null)`

		//更新销售订单主表已发数量
		var updatexsddsql = `
		    merge into shipSRM_fhtzd a using (select mainid,sum(sfsl) sfsl  from 
		    shipSRM_fhtzd_mx where sfsl is not null group by mainid  ) b
		    on a.id=b.mainid and a.create_time >'2024-10-10'
		    when matched then update set 
		    zsfsl=b.sfsl;
`

		var updateyfslsql = `
            merge into shipSRM_xsckd a using (select mainid,sum(CAST(yfsl AS FLOAT)) yfsl  from 
            shipSRM_xsckd_mx where yfsl is not null group by mainid  ) b
            on a.id=b.mainid 
            when matched then update set 
            zyfsl=b.yfsl;`
		var updatesfslsql = `
            merge into shipSRM_xsckd a using (select mainid,sum(CAST(sfsl AS FLOAT)) sfsl  from 
            shipSRM_xsckd_mx where sfsl is not null group by mainid  ) b
            on a.id=b.mainid and a.create_time >'2024-10-10'
            when matched then update set 
            zsfsl=b.sfsl;`


			var updatefhtzdsql = `update shipSRM_fhtzd set xtzt='已下推'
			where id in (select ydid from shipSRM_xsckd_mx) and create_time >'2024-10-10'`
   
					//如果明细不存在已下推 更新为 null
		   var updatefhtzdsql2 = `update shipSRM_fhtzd set xtzt=null where id 
		   not in (select ydid from shipSRM_xsckd_mx where ydid is not null)
		    and create_time >'2024-10-10'`
	   
   


		await db.update(updatesql)
		await db.update(updatefhtzdsql_sfsl)
		await db.update(updatexsddsql)
		await db.update(updateyfslsql)
		await db.update(updatesfslsql)
		await db.update(updatefhtzdsql)
		await db.update(updatefhtzdsql2)

		resolve(true)
	})
}

/**
 * 采购入库更新采购订单
 * @returns   
 */
async function use_cgrkd_to_cgdd() {
	return new Promise(async function (resolve, reject) {
		var db = new Msdb();

		//如果删除采购入库单没有 更新采购订单入库数量为0 
		var udpCgddSql = `update shipSRM_cgdd_mx set rksl=0 where id not in (select b.ydmxid from shipSRM_cgrk_detail
				a left JOIN shipSRM_sltzd_mx b ON a.ydmxid = b.id)`

		//从发货通知单明细表更新到采购订单原料明细
		//shipSRM_fhtzd_mx to shipSRM_xsdd_mx
		var updatesql = `
		merge into shipSRM_cgdd_mx a using  (
		select b.ydmxid,sum(a.sl) sl from shipSRM_cgrk_detail a left JOIN shipSRM_sltzd_mx b ON 
		(a.ydmxid = b.id)
		 where a.sl is not null group by b.ydmxid 
		 ) b 
		on a.id=b.ydmxid 
		when matched then update set 
		rksl=b.sl  ;`
        //如果明细不存在 更新为null
		var updatecgddsql2 = `update shipSRM_cgdd_mx set rksl=null where id not in (select b.ydmxid from shipSRM_cgrk_detail a left JOIN shipSRM_sltzd_mx b ON a.ydmxid = b.id where b.ydmxid is not null)`

		//更新采购订单主表总入库数量
		var updatecgddsql = `
		merge into shipSRM_cgdd a using (select mainid,sum(rksl) rksl  from 
		shipSRM_cgdd_mx where rksl is not null group by mainid  ) b
		on a.id=b.mainid 
		when matched then update set 
		zrksl=b.rksl;`




		await db.update(updatesql)
		await db.update(updatecgddsql)
		await db.update(updatecgddsql2)
		await db.update(udpCgddSql)

		await use_cgrkd_to_sltzd()
		resolve(true)
	})
}

/**
 * 采购入库单更新收料报检单
 * @returns   
 */
async function use_cgrkd_to_sltzd() {
	return new Promise(async function (resolve, reject) {
		var db = new Msdb();
		//从采购入库单明细表更新到收料报检单
		var updatesql = `
		    merge into shipSRM_sltzd_mx a using  (
		    	select ydmxid,sum(CAST(sl AS FLOAT)) sl from shipSRM_cgrk_detail where sl is not null group by ydmxid 
		     ) b 
		    on a.id=b.ydmxid 
		    when matched then update set 
		    rksl=b.sl ,sysl=a.slsl-b.sl;`

		//更新销售订单主表入库数量
		var updatecgddsql = `
		     merge into shipSRM_sltzd a using (select mainid,sum(rksl) rksl  from 
		     shipSRM_sltzd_mx where rksl is not null group by mainid  ) b
		     on a.id=b.mainid 
		     when matched then update set 
		     zrksl=b.rksl`
		//更新采购入库单主表入库数量	 
		var updateyfslsql = `
            merge into shipSRM_cgrk a using (select mainid,sum(CAST(sl AS FLOAT)) sl  from 
            shipSRM_cgrk_detail where sl is not null group by mainid  ) b
            on a.id=b.mainid 
            when matched then update set 
            zsl=b.sl;`
		//更新采购入库单主收料数量数量	 
		var updatesfslsql = `
            merge into shipSRM_cgrk a using (select mainid,sum(CAST(slsl AS FLOAT)) slsl  from 
            shipSRM_cgrk_detail where slsl is not null group by mainid  ) b
            on a.id=b.mainid 
            when matched then update set 
			zslsl=b.slsl;`

		//增加逻辑 如果 没有采购入库单 更新收料报检单对应字段为0,下推状态为空
		var updateSltzdsql = `update shipSRM_sltzd set zslsl=0,zrksl=0,djzt='正常' where id not in (select ydid from shipSRM_cgrk_detail where ydid is not null )`

		var updateSltzdDjzt =`merge into shipSRM_sltzd a using (
               select  mainid,count(*) zsl,
                sum (case when ((xtzt = '已下推' and slsl >= cgsl) or xtlx = '免检下推') THEN 1 ELSE 0 END) as mxxtsl
				from  shipSRM_sltzd_mx GROUP BY  mainid  
             ) b
             on a.id=b.mainid and zsl=mxxtsl
             when matched then update set 
             djzt='关闭';`

		//增加逻辑 如果 没有采购入库单	 更新收料报检单对应字段为0,下推状态为空
		var updateSltzdMxsql = `update shipSRM_sltzd_mx set xtzt='',rksl=0,sysl=slsl where id not in (select ydmxid from shipSRM_cgrk_detail where ydmxid is not null )`


		await db.update(updatesql)
		await db.update(updatecgddsql)
		await db.update(updateyfslsql)
		await db.update(updatesfslsql)
		await db.update(updateSltzdsql)
		await db.update(updateSltzdMxsql)
		await db.update(updateSltzdDjzt)
		resolve(true)
	})
}

module.exports = {
	use_fhtzd_to_xsdd,
	use_sltzd_to_cgdd,
	use_xsckd_to_xsdd,
	use_cgrkd_to_cgdd,
	use_cgrkd_to_sltzd,
	use_qdgmx_qgd
};